contents

윈도우 함수(window function) 는 현재 행과 어떤 방식으로든 관련된 테이블 행들의 집합에 대해 계산을 수행하는 강력한 SQL 기능입니다. 여러 행을 하나의 출력 행으로 축소하는 일반 집계 함수(GROUP BY와 함께 사용되는 SUM() 등)와 달리, 윈도우 함수는 모든 개별 행에 대해 값을 반환합니다.

이렇게 생각하면 쉽습니다. GROUP BY 집계는 모든 판매 영수증을 믹서기에 넣어 총액 하나를 얻는 것과 같습니다. 반면, 윈도우 함수는 각 영수증을 개별적으로 살펴보면서, 각각에 "이것은 오늘 세 번째로 높은 판매액이었어"라거나 "이 시점까지의 누적 합계는 50만원이었어"와 같은 메모를 남기는 것과 같습니다. 원래의 영수증(행)은 그대로 보존됩니다.

윈도우 함수가 해결하는 문제

윈도우 함수 이전에는 특정 유형의 분석을 SQL로 수행하기가 매우 어려웠습니다. 다음과 같은 질문들을 생각해 보세요.

• "각 부서 내에서 직원들의 급여를 기준으로 순위를 매겨라."
• "매월 매출에 대해, 이전 달의 매출은 얼마였는가?"
• "올해의 누적 매출 합계는 얼마인가?"

이러한 질문에 답하려면 복잡한 셀프 조인(self-join), 상관 서브쿼리, 또는 절차적 로직이 필요했으며, 이는 느리고, 읽기 어렵고, 유지보수하기 힘들었습니다. 윈도우 함수는 이에 대한 우아하고 효율적인 해결책을 제공합니다.

핵심 문법: OVER() 절 🖼️

윈도우 함수의 마법은 함수가 작동할 행의 "창(window)"을 정의하는 OVER() 절에 있습니다.

SQL

함수_이름(표현식) OVER (
    [PARTITION BY 표현식, ...]
    [ORDER BY 표현식, ...]
    [ROWS 또는 RANGE 프레임_절]
)

1. PARTITION BY ("그룹")

이 하위 절은 행들을 파티션, 즉 그룹으로 나눕니다. 윈도우 함수는 각 파티션에 독립적으로 적용되며 다음 파티션에서는 재시작됩니다. PARTITION BY를 생략하면 전체 결과 집합이 단일 파티션으로 취급됩니다.

• 예시: PARTITION BY department는 영업 부서, 엔지니어링 부서 등에 대해 별도의 창을 만듭니다.

2. ORDER BY ("순서")

이는 각 파티션 내에서 행의 순서를 지정합니다. 순위에 의존하는 함수(예: 순위 매기기, 누적 합계)에 매우 중요합니다.

• 예시: ORDER BY salary DESC는 각 부서 내에서 직원들을 급여가 높은 순에서 낮은 순으로 정렬합니다.

3. ROWS/RANGE ("프레임") - 고급

이 하위 절은 파티션 내에서 더 작고 움직이는 윈도우 프레임을 지정합니다. 예를 들어, 3일 이동 평균을 계산하기 위해 프레임을 "현재 행과 이전 2개 행"으로 정의할 수 있습니다.

윈도우 함수의 종류

1. 집계 윈도우 함수 📊

표준 집계 함수(SUM, AVG, COUNT, MAX, MIN)에 OVER() 절을 추가하여 윈도우 함수로 사용할 수 있습니다.

예시: 각 부서의 총 급여와 각 직원의 누적 급여 합계 구하기

SELECT
    name,
    department,
    salary,
    -- 각 부서의 총 급여 구하기
    SUM(salary) OVER (PARTITION BY department) AS total_dept_salary,
    -- 각 부서 내에서 이름순으로 급여의 누적 합계 구하기
    SUM(salary) OVER (PARTITION BY department ORDER BY name) AS running_total
FROM
    employees;

2. 순위 윈도우 함수 🥇

파티션 내에서 행의 순위를 매기는 데 사용됩니다.

• ROW_NUMBER(): 각 행에 고유하고 순차적인 정수를 할당합니다 (1, 2, 3, 4...).
• RANK(): ORDER BY 절에 따라 순위를 할당합니다. 동점자는 같은 순위를 받고, 다음 순위는 건너뜁니다 (1, 2, 2, 4...).
• DENSE_RANK(): RANK()와 유사하지만, 동점자 이후에도 순위를 건너뛰지 않습니다 (1, 2, 2, 3...).

예시: 각 부서 내에서 급여별로 직원 순위 매기기

SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk
FROM
    employees;

3. 값 윈도우 함수 (오프셋 함수) ➡️

현재 행을 기준으로 다른 행의 데이터에 접근할 수 있게 해주는 함수입니다.

• LAG(표현식, 오프셋, [기본값]): 이전 행의 데이터에 접근합니다.
• LEAD(표현식, 오프셋, [기본값]): 다음 행의 데이터에 접근합니다.
• FIRST_VALUE(표현식): 윈도우 프레임의 첫 번째 행의 값을 가져옵니다.
• LAST_VALUE(표현식): 윈도우 프레임의 마지막 행의 값을 가져옵니다.

예시: 전월 대비 매출 성장률 계산하기

SELECT
    sale_month,
    monthly_sales,
    LAG(monthly_sales, 1, 0) OVER (ORDER BY sale_month) AS previous_month_sales,
    monthly_sales - LAG(monthly_sales, 1, 0) OVER (ORDER BY sale_month) AS month_over_month_growth
FROM
    monthly_sales_summary;

결론적으로, 윈도우 함수는 현대 SQL의 믿을 수 없을 정도로 강력하고 표현력이 풍부한 기능입니다. 이를 통해 순위, 누적 합계, 행 간 비교와 같은 복잡한 분석 작업을 깔끔하고, 읽기 쉬우며, 매우 효율적인 방식으로 작성할 수 있습니다.

references